------------------STEP 1:DOANH SO BAO LANH-----------------------------------------------

--DROP TABLE BAO_LANH_20161001_20161031
--DROP TABLE BAO_LANH_RN_20161001_20161031
--DROP TABLE DOANH_SO_BAO_LANH_20161001_20161031

DECLARE @MONTH1 NVARCHAR(8)
DECLARE @MONTH2 NVARCHAR(8)
SET @MONTH1='20161001'
SET @MONTH2='20161031'

EXEC('
	SELECT  * INTO BAO_LANH_'+@MONTH1+'_'+@MONTH2+'
	FROM SERVER16.[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB] A
	WHERE A.BUSINESS_DATE BETWEEN '''+@MONTH1+''' AND '''+@MONTH2+'''
	AND A.CO_CODE NOT IN (SELECT BRANCH_CODE FROM ANHCP.CEO_PERFORMANCE.DBO.BRANCHOFF WHERE BRANCH_CODE <> ''VN0010289'')
	AND CUS_SEGMENT NOT IN (''KHCN'',''HH'') 
	AND 
	 ((CATEGORY IN (''28609'')
		AND (A.FUND_PROGRAM LIKE ''%TAI TRO CHUOI HTC%''OR A.FUND_PROGRAM  LIKE ''%VEAM'')
		OR( CATEGORY IN (''28103'',''28104'',''28113'',''28116'',''28109'',''28114''))
		OR( CATEGORY IN (''28102'')
		   AND A.FUND_PROGRAM NOT LIKE ''%TAI TRO CHUOI HTC%'' AND A.FUND_PROGRAM NOT LIKE ''%VEAM'')))')
EXEC(' 
       SELECT ROW_NUMBER() OVER( PARTITION BY MD_ID ORDER BY BUSINESS_DATE) AS RN, * 
       INTO BAO_LANH_RN_'+@MONTH1+'_'+@MONTH2+'
       FROM BAO_LANH_'+@MONTH1+'_'+@MONTH2+'')  
EXEC('
       SELECT CONVERT(NVARCHAR(6),BUSINESS_DATE,112) AS DATE_REPORT,*,ORG_AMOUNT*MID_RATE AS AMOUNT_QD 
       ,CASE WHEN CATEGORY=''28102'' THEN '' BAO LANH THANH TOAN''
       WHEN CATEGORY=''28103'' THEN '' BAO LANH THUC HIEN HOP DONG''
       WHEN CATEGORY=''28104''THEN '' BAO LANH DU THAU''
       WHEN CATEGORY=''28113'' THEN '' BAO LANH HOAN TAM UNG''
       WHEN CATEGORY=''28116'' THEN '' BAO LANH THANH TOAN THE''
       WHEN CATEGORY=''28109'' THEN '' BAO LANH BAO HANH''
       WHEN CATEGORY=''28114'' THEN '' BAO LANH DOI UNG'' 
       WHEN CATEGORY=''28609'' THEN '' BAO LANH TONG''
       END BAO_LANH_TYPE INTO DOANH_SO_BAO_LANH_'+@MONTH1+'_'+@MONTH2+'
       FROM BAO_LANH_RN_'+@MONTH1+'_'+@MONTH2+' A
       LEFT JOIN (SELECT * FROM SERVER16.VPB_WHR2.DBO.FOCURR_SAVE WHERE SAVE_DATE='''+@MONTH2+''') B
       ON A.CCY=B.CODE
       WHERE RN=1
       AND VALUE_DATE  BETWEEN '''+@MONTH1+''' AND '''+@MONTH2+'''')
     
EXEC( 'INSERT TRADE_DOANH_SO_BAO_LANH
       SELECT * 
       FROM DOANH_SO_BAO_LANH_'+@MONTH1+'_'+@MONTH2+'')
       
       --DELETE FROM TRADE_DOANH_SO_BAO_LANH
       
--------------------------------------------STEP 2:SO DU BAO LANH LC--------------------------------------    
EXEC(' 
	SELECT CONVERT(NVARCHAR(6),BUSINESS_DATE,112) AS DATE_REPORT,BUSINESS_DATE,CUST_ID,DAO ,CATEGORY,
	(CASE WHEN GL IN(''9220'',''9260'',''9270'',''9280'',''9210'') THEN ''CAC NGHIEP VU BAO LANH''
	WHEN GL IN(''9250'') THEN ''CAC CAM KET TRONG NGHIEP VU L/C''
	WHEN GL IN(''9231'',''9232'',''9233'',''9234'',''9821'',''9822'',''9823'',''9824'',''9825'',''9241'',''9242'',''9243'',''9244'',
	''9245'',''9291'',''9293'',''9299'') THEN ''CAM KET KHAC''
	ELSE ''UNDEFINED''
	END) AS GL_DESC
		  ,[COMPANY]
		  ,[AMOUNT_LCY] INTO SO_DU_BAO_LANH_LC_'+@MONTH2+'
	  FROM SERVER16.[VPB_WHR2].[DBO].[CRB_ALL]
	  WHERE BUSINESS_DATE = '''+@MONTH2+'''
	  AND SEGMENT NOT IN (''KHCN'')
	  AND COMPANY NOT IN (''VN0010326'',''VN0010327'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010007'',''VN0010005'',''VN0010009'',''VN0010336'',''VN0010335'',''VN0010260'',''VN0010289'',''VN0010338'',''VN0010010'',''VN0010011'',''VN0010012'',''VN0010344'',''VN0010006'')
	  AND GL IN(''9220'',''9260'',''9270'',''9280'',''9210'',''9250'',''9231'',''9232'',''9233'',''9234'',''9821'',''9822'',''9823'',''9824'',''9825'',''9241'',''9242'',''9243'',''9244'',
	''9245'',''9291'',''9293'',''9299'')')

      
EXEC('
       INSERT SO_DU_BAO_LANH_LC
       SELECT * 
       FROM SO_DU_BAO_LANH_LC_'+@MONTH2+'')


	